﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace SalesSys.Comm.DAL
{
    public class WhereCondition
    {
        public WhereCondition()
        {
            tool = new LogicalItem() { source = this };
        }
      

        public LogicalItem And
        {
            get { return tool.SetNowRelationType(false); }
        }
        public LogicalItem Or
        {
            get { return tool.SetNowRelationType(true); }
        }

        private LogicalItem tool;
        private List<SqlItem> conditionList = new List<SqlItem>();

        public List<SqlItem> ConditionList
        {
            get { return conditionList; }
        }
        public SqlItem[] NotNullValueConditionList()
        {
           return SqlItem.RemoveNullValueItem(conditionList);
        }
        public object[][] ToConditionArray()
        {
            return conditionList.Select(p => new[] { p.IsOr?1:0, (int)p.ComparisonType, p.Name, p.Value }).ToArray();
        }
        public static SqlItem[] GetCondition(object[][]  obj)
        {
            var list = new List<SqlItem>();
            foreach (var item in obj)
            {
                var array=item as object[];
                if (array == null || array.Length < 4)
                    continue;
                var sqlItem = new SqlItem();
                sqlItem.IsOr =Convert.ToInt32( array[0])==1;
                sqlItem.ComparisonType = (WhereCondition.Comparison)Convert.ToInt32(array[1]);
                sqlItem.Name = array[2] as string;
                sqlItem.Value = array[3];
                list.Add(sqlItem);
            }
            return list.ToArray(); 
        }
        #region internal class


        public class LogicalItem
        {
            public WhereCondition source;
            bool nowRelationType;
            public LogicalItem SetNowRelationType(bool relationType)
            {
                nowRelationType = relationType;
                return this;
            }
            /// <summary>
            /// 当前条件关系，false=and,
            /// </summary>
            public bool NowRelationType
            {
                get
                {
                    return nowRelationType;
                }
            }


            public WhereCondition Equal(string fieldName, object value)
            {

                return AddCondition(Comparison.Equal, fieldName, value);
            }

            public WhereCondition NotEqual(string fieldName, object value)
            {
                return AddCondition(Comparison.NotEqual, fieldName, value);
            }

            public WhereCondition GreaterThan(string fieldName, object value) { return AddCondition(Comparison.GreaterThan, fieldName, value); }
            public WhereCondition GreaterOrEqual(string fieldName, object value) { return AddCondition(Comparison.GreaterOrEqual, fieldName, value); }
            public WhereCondition LessThan(string fieldName, object value) { return AddCondition(Comparison.LessThan, fieldName, value); }
            public WhereCondition LessOrEqual(string fieldName, object value) { return AddCondition(Comparison.LessOrEqual, fieldName, value); }
            public WhereCondition Like(string fieldName, object value) { return AddCondition(Comparison.Like, fieldName, value); }
            public WhereCondition NotLike(string fieldName, object value) { return AddCondition(Comparison.NotLike, fieldName, value); }
            public WhereCondition IsNull(string fieldName) { return AddCondition(Comparison.IsNull, fieldName, null); }
            public WhereCondition IsNotNull(string fieldName) { return AddCondition(Comparison.IsNotNull, fieldName, null); }
            public WhereCondition In(string fieldName, params object[] value) { return AddCondition(Comparison.In, fieldName, value); }
            public WhereCondition NotIn(string fieldName, params object[] value) { return AddCondition(Comparison.NotIn, fieldName, value); }
            public WhereCondition Between(string fieldName, object value, object value2) { return AddConditionByArray(Comparison.Between, fieldName, value, value2); }
            public WhereCondition StartsWith(string fieldName, object value) { return AddCondition(Comparison.StartsWith, fieldName, value); }
            public WhereCondition EndsWith(string fieldName, object value) { return AddCondition(Comparison.EndsWith, fieldName, value); }

            private WhereCondition AddConditionByArray(Comparison nodeType, string fieldName, params object[] values)
            {
              return  AddCondition(nodeType, fieldName, values);
            }
            private WhereCondition AddCondition(Comparison nodeType, string fieldName, object value)
            {
                if (string.IsNullOrEmpty(fieldName))
                    return source;
                SqlItem s = new SqlItem();
                s.IsOr = nowRelationType;
                s.ComparisonType = nodeType;
                s.Name = fieldName;
                s.Value = value;
                source.ConditionList.Add(s);
                return source;
            }
           
        }

        #endregion
        public enum Comparison
        {
            /// <summary>
            /// 等于号 =
            /// </summary>
            Equal,
            /// <summary>
            /// 不等于号 <>
            /// </summary>
            NotEqual,
            /// <summary>
            /// 大于号 >
            /// </summary>
            GreaterThan,
            /// <summary>
            /// 大于或等于 >=
            /// </summary>
            GreaterOrEqual,
            /// <summary>
            /// 小于 <
            /// </summary>
            LessThan,
            /// <summary>
            /// 小于或等于 <=
            /// </summary>
            LessOrEqual,
            /// <summary>
            /// 模糊查询 Like
            /// </summary>
            Like,
            /// <summary>
            /// 模糊查询  Not Like
            /// </summary>
            NotLike,
            /// <summary>
            /// is null
            /// </summary>
            IsNull,
            /// <summary>
            /// is not null
            /// </summary>
            IsNotNull,
            /// <summary>
            /// in
            /// </summary>
            In,
            /// <summary>
            /// not in
            /// </summary>
            NotIn,
            Between,
            StartsWith,
            EndsWith
        }
    }
    public struct SqlItem<T>
    {
        public bool IsOr;
        public WhereCondition.Comparison ComparisonType;
        public string Name;
        public T Value;

    }
    public struct SqlItem 
    {     
        public bool IsOr;
        public WhereCondition.Comparison ComparisonType;
        public string Name;
        public object Value;
        SqlItem<T> ToType<T>()
        {
            return new SqlItem<T>() { ComparisonType = ComparisonType, IsOr = IsOr, Name = Name, Value = (T)Value };
        }
        public static WhereCondition CreateWhere()
        {
            return new WhereCondition();
        }

        public static SqlItem[] RemoveNullValueItem(IEnumerable<SqlItem> list)
        {
            return list.Where(p =>
              {
                  if (p.ComparisonType != WhereCondition.Comparison.IsNull && p.ComparisonType != WhereCondition.Comparison.IsNotNull)
                      return p.Value != null;
                  return true;
              }).ToArray();
        }
        #region Check
        
        public static  CheckParam<T1,T2,T3,T4,T5,T6> CreateCheck<T1,T2,T3,T4,T5,T6>(params string[] names)
        {
            return new CheckParam<T1, T2, T3, T4, T5, T6>() { ParamNames = names };
        }
        public static CheckParam<T1> CreateCheck<T1>(params string[] names)
        {
            return new CheckParam<T1>() { ParamNames = names };
        }
        public static CheckParam<T1, T2> CreateCheck<T1, T2>(params string[] names)
        {
            return new CheckParam<T1, T2>() { ParamNames = names };
        }
        public static CheckParam<T1, T2, T3> CreateCheck<T1, T2, T3>(params string[] names)
        {
            return new CheckParam<T1, T2, T3>() { ParamNames = names };
        }

        public class CheckParam<T1>
        {
            private string[] paramNames;

            public string[] ParamNames
            {
                get { return paramNames; }
                set { paramNames = value; }
            }
           // Func<IEnumerable<SqlItem>, string> Fun;
            public void SetParamNames(params string[] names)
            {
                paramNames = names;
            }
            
            public string Check(IEnumerable<SqlItem> items,Func<IEnumerable<SqlItem>, string> fun=null)
            {
                string msg = null;
                foreach (var item in items)
                {
                    var index = paramNames.ToList().IndexOf(item.Name);
                    if (index < 0)
                        return item.Name + ":未知查询字段";
                    if (!CanChangeType(index, item.Value))
                    {
                        return msg = item.Name + ":检查不通过";
                    }
                }
                if(fun!=null)  msg=fun(items);
                return msg;
            }
            public virtual bool CanChangeType(int typeIndex, object obj)
            {
                try
                {
                    TryChange(typeIndex, obj);
                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            }
            public virtual bool TryChange( int typeIndex,object obj)
            {
                if (typeIndex == 0)
                {
                    var cObj= (T1)obj;
                }
                return true;
            }
        }
        public class CheckParam<T1, T2>:CheckParam<T1>
        {
            public override bool TryChange(int typeIndex, object obj)
            {
                if (typeIndex == 1)
                {
                    var o = (T2)obj;
                    return true;
                }
                return base.TryChange(typeIndex, obj);
            }
        }
        public class CheckParam<T1, T2,T3> : CheckParam<T1,T2>
        {
            public override bool TryChange(int typeIndex, object obj)
            {
                if (typeIndex == 2)
                {
                    var o = (T3)obj;
                    return true;
                }
                return base.TryChange(typeIndex, obj);
            }
        }
        public class CheckParam<T1,T2,T3,T4,T5,T6>
        {
            public string[] ParamNames;

            public string Check(IEnumerable<SqlItem> items)
            {
                string msg = null;
                foreach (var item in items)
                {
                    var index = ParamNames.ToList().IndexOf(item.Name);
                    if (index < 0)
                        return item.Name + ":未知查询字段";
                    if (!CanChangeType(index, item.Value))
                    {
                        return msg = item.Name + ":检查不通过";
                    }
                }
                return msg;
            }
            public bool CanChangeType(int typeIndex, object obj)
            {
                object cObj = null;
                try
                {
                    switch (typeIndex)
                    {
                        case 0: cObj = (T1)obj;
                            break;
                        case 1: cObj = (T2)obj;
                            break;
                        case 2: cObj = (T3)obj;
                            break;
                        case 3: cObj = (T4)obj;
                            break;
                        case 4: cObj = (T5)obj;
                            break;
                        case 5: cObj = (T6)obj;
                            break;
                        default:
                            break;
                    }
                }
                catch (Exception)
                {
                    return false;
                }
                return true;
            }
        }
        #endregion

    }

    public class SimpleDataParam
    {

        public DbType DbType { get; set; }


        public string ParameterName { get; set; }


        public object Value { get; set; }
    }
    

    public interface IConditionAdapter<TWhere>
    {
        TWhere ToWhere(IEnumerable<SqlItem> list);
    }
    public abstract class BaseSqlTextCondition<TParam> : IConditionAdapter<KeyValuePair<string, IEnumerable<TParam>>>
    {

        public BaseSqlTextCondition(string paramPrefix="@", string paramKey="P")
        {
            parameterPrefix = paramPrefix;
            parameterKey = paramKey;
        }
        /// <summary>
        /// 用来拼接SQL语句
        /// </summary>
        StringBuilder conditionBuilder = new StringBuilder();

        /// <summary>
        /// 是否生成带参数的sql
        /// </summary>
        bool isBuildParameterSql = true;
        /// <summary>
        /// 参数列表
        /// </summary>
        public List<TParam> parameterList = new List<TParam>();
        int index = 0;

      protected  readonly string parameterPrefix = "@";
      protected readonly string parameterKey = "P";
      protected readonly string and = " AND ";
      protected readonly string or = " OR ";
        public KeyValuePair<string, IEnumerable<TParam>> ToWhere(IEnumerable<SqlItem> list)
        {
            bool isFirst = true;
            foreach (var item in list)
            {
                if (isFirst)
                {
                      AddCondition(null, false, item.Name, item.ComparisonType, item.Value);
                      isFirst = false;
                }else
                AddCondition(item.IsOr, false, item.Name, item.ComparisonType, item.Value);
            }
            return new KeyValuePair<string, IEnumerable<TParam>>(conditionBuilder.ToString(), parameterList.ToArray());
        }




        #region 公共方法

        public void AddCondition(bool? isOr, bool isFieldRight, string fieldName, WhereCondition.Comparison comparison, object value)
        {
            if (string.IsNullOrEmpty(fieldName))
                return;
            if (isOr != null)
            {
                if (isOr.Value)
                    conditionBuilder.Append(or);
                else
                    conditionBuilder.Append(and);
            }
            if (isFieldRight)
                switch (comparison)
                {
                    case WhereCondition.Comparison.Equal:
                    case WhereCondition.Comparison.NotEqual:
                    case WhereCondition.Comparison.GreaterThan:
                    case WhereCondition.Comparison.GreaterOrEqual:
                    case WhereCondition.Comparison.LessThan:
                    case WhereCondition.Comparison.LessOrEqual:
                        this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldName(value + ""));
                        break;
                    default: throw new ArgumentException("不能处理的列相互比较,列与列对比只支持大小、相等的比较！");
                }
            else
                AddCondition(fieldName, comparison, value);

        }


        /// <summary>
        /// 添加条件
        /// </summary>
        /// <param name="comparison">比较符类型</param>
        /// <param name="fieldName">字段名称</param>
        /// <param name="fieldValue">字段值，注：Between时，此字段必须填两个值</param>
        /// <returns>返回ConditionHelper</returns>
        public void AddCondition(string fieldName, WhereCondition.Comparison comparison, object value)
        {
            var colName = GetFieldName(fieldName);
            switch (comparison)
            {
                case WhereCondition.Comparison.Equal:
                case WhereCondition.Comparison.NotEqual:
                case WhereCondition.Comparison.GreaterThan:
                case WhereCondition.Comparison.GreaterOrEqual:
                case WhereCondition.Comparison.LessThan:
                case WhereCondition.Comparison.LessOrEqual:
                    this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(value, fieldName));
                    break;
                case WhereCondition.Comparison.IsNull:
                case WhereCondition.Comparison.IsNotNull:
                    this.conditionBuilder.AppendFormat("{0}{1}", GetFieldName(fieldName), GetComparisonOperator(comparison));
                    break;
                case WhereCondition.Comparison.Like:
                case WhereCondition.Comparison.NotLike:
                    this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(string.Format("%{0}%", value), fieldName));
                    break;
                case WhereCondition.Comparison.In:
                case WhereCondition.Comparison.NotIn:
                    this.conditionBuilder.AppendFormat("{0}{1}({2})", GetFieldName(fieldName), GetComparisonOperator(comparison), string.Join(",", GetFieldValue(value as object[], fieldName)));
                    break;
                case WhereCondition.Comparison.StartsWith:
                    this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(string.Format("{0}%", value), fieldName));
                    break;
                case WhereCondition.Comparison.EndsWith:
                    this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(string.Format("%{0}", value), fieldName));
                    break;
                case WhereCondition.Comparison.Between:
                    var fieldValue = value as object[];
                    this.conditionBuilder.AppendFormat("{0}{1}{2} AND {3}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(fieldValue[0], fieldName), GetFieldValue(fieldValue[1], fieldName));
                    break;
                default:
                    throw new ArgumentException("未实现");
            }
            return;
        }


        public override string ToString()
        {
            return this.conditionBuilder.ToString();
        }

        #endregion

        #region 私有方法

        private string GetFieldValue(object fieldValue, string fieldName)
        {
            if (isBuildParameterSql == false)
            {

                return string.Format("'{0}'", fieldValue);
            }
            else
            {
                return AddParameter(fieldValue, fieldName);
            }
        }

        /// <summary>
        /// 取得字段值
        /// </summary>
        /// <param name="fieldValue"></param>
        /// <returns></returns>
        private string GetFieldValue(object[] fieldValue, string fieldName)
        {
            if (isBuildParameterSql == false)
            {
                if (fieldValue.Length < 2)
                {
                    return string.Format("'{0}'", fieldValue[0]);
                }
                else
                {
                    return string.Format("'{0}'", string.Join("','", fieldValue));
                }
            }
            else
            {
                if (fieldValue.Length < 2)
                {
                    return AddParameter(fieldValue[0], fieldName);
                }
                else
                {
                    List<string> parameterNameList = new List<string>();
                    foreach (var value in fieldValue)
                    {
                        parameterNameList.Add(AddParameter(value, fieldName));
                    }
                    return string.Join(",", parameterNameList);
                }
            }
        }

        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="fieldValue"></param>
        /// <returns></returns>
        private string AddParameter(object fieldValue, string fieldName)
        {
            index++;
            string parameterName = CreateParamName(fieldName,index);
            parameterList.Add(CreateParameter(fieldName, fieldValue,index));
            return parameterName;
        }

        protected virtual string CreateParamName(string fieldName,int ind)
        {
            string parameterName = string.Format("{0}{1}{2}_{3}", parameterPrefix, parameterKey, ind, fieldName);
            return parameterName;
        }
        protected abstract TParam CreateParameter(string fieldName, object value,int index);


        protected virtual string GetFieldName(string fieldName)
        {

            if (isBuildParameterSql)
            {
                var findStr = fieldName.ToCharArray().FirstOrDefault(p => !(char.IsLetterOrDigit(p) || p == '_'));
                if (findStr != 0)
                    throw new ArgumentException("列名中不允许包含除字母、数字、下划线以外的字符");
            }
            return string.Format("[{0}]", fieldName);
        }
        private static string GetComparisonOperator(WhereCondition.Comparison comparison)
        {
            string result = string.Empty;
            switch (comparison)
            {
                case WhereCondition.Comparison.Equal:
                    result = " = ";
                    break;
                case WhereCondition.Comparison.NotEqual:
                    result = " <> ";
                    break;
                case WhereCondition.Comparison.GreaterThan:
                    result = " > ";
                    break;
                case WhereCondition.Comparison.GreaterOrEqual:
                    result = " >= ";
                    break;
                case WhereCondition.Comparison.LessThan:
                    result = " < ";
                    break;
                case WhereCondition.Comparison.LessOrEqual:
                    result = " <= ";
                    break;
                case WhereCondition.Comparison.Like:
                case WhereCondition.Comparison.StartsWith:
                case WhereCondition.Comparison.EndsWith:
                    result = " LIKE ";
                    break;
                case WhereCondition.Comparison.NotLike:
                    result = " NOT LIKE ";
                    break;
                case WhereCondition.Comparison.IsNull:
                    result = " IS NULL ";
                    break;
                case WhereCondition.Comparison.IsNotNull:
                    result = " IS NOT NULL ";
                    break;
                case WhereCondition.Comparison.In:
                    result = " IN ";
                    break;
                case WhereCondition.Comparison.NotIn:
                    result = " NOT IN ";
                    break;
                case WhereCondition.Comparison.Between:
                    result = " BETWEEN ";
                    break;
            }
            return result;
        }
        #endregion




    }
  
}
